﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

namespace Elpur
{
    public partial class _Default : Page
    {
        private const string DB_USERNAME = "elpurdb@xmapfxxhmm";
        private const string DB_PASSWORD = "9jUdsMDio/T9qgXROmWXVQUnfN5WNEptdhXEdftWFH4=";
        private const string DB_DATASOURCE = "tcp:xmapfxxhmm.database.windows.net,1433";
        public string _countryname = "N/A";
        public string _capitalcity = "N/A";
        public string _regioncode = "N/A";
        public string _regionname = "N/A";
        public string _codeiso = "N/A";
        public string _incomelevel = "N/A";
        public string _lendingtypename = "N/A";
        public string _lendingtypecode = "N/A";
        public decimal _latitude;
        public decimal _longitude;

        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnectionStringBuilder _ConnStringBuilder = new SqlConnectionStringBuilder();
            _ConnStringBuilder.DataSource = DB_DATASOURCE;
            _ConnStringBuilder.InitialCatalog = "Elpur_DB";
            _ConnStringBuilder.Encrypt = true;
            _ConnStringBuilder.TrustServerCertificate = false;
            _ConnStringBuilder.UserID = DB_USERNAME;
            _ConnStringBuilder.Password = DB_PASSWORD;

            using (SqlConnection conn = new SqlConnection(_ConnStringBuilder.ToString()))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT CountryName FROM GetCountries";
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ListItem li = _countryList.Items.FindByValue(reader["CountryName"].ToString());
                            if (li == null)
                                _countryList.Items.Add(new ListItem(reader["CountryName"].ToString(), reader["CountryName"].ToString()));
                        }
                    }
                    cmd.CommandText = "SELECT * FROM GetCountries WHERE CountryName='" + _countryList.Text + "'";
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        reader.Read();
                        decimal.TryParse(reader["Latitude"].ToString(), out _latitude);
                        decimal.TryParse(reader["Longitude"].ToString(), out _longitude);
                        
                        if(reader["CountryName"].ToString()!="")
                            _countryname = reader["CountryName"].ToString();
                        
                        if (reader["CapitalCity"].ToString() != "")
                            _capitalcity = reader["CapitalCity"].ToString();

                        if (reader["RegionName"].ToString() != "")
                            _regionname = reader["RegionName"].ToString();

                        if (reader["RegionCode"].ToString() != "")
                            _regioncode = reader["RegionCode"].ToString();

                        if (reader["CodeIso3Letter"].ToString() != "")
                            _codeiso = reader["CodeIso3Letter"].ToString();

                        if (reader["IncomeLevelName"].ToString() != "")
                            _incomelevel = reader["IncomeLevelName"].ToString();

                        if (reader["LendingTypeCode"].ToString() != "")
                            _lendingtypecode = reader["LendingTypeCode"].ToString();

                        if (reader["LendingTypeName"].ToString() != "")
                            _lendingtypename = reader["LendingTypeName"].ToString();
                    }
                    _info_countryname.Text = _countryname;
                    _info_capitalcity.Text = _capitalcity;
                    _info_regionname.Text = _regionname;
                    _info_regioncode.Text = _regioncode;
                    _info_codeiso.Text = _codeiso;
                    _info_incomelevel.Text = _incomelevel;
                    _info_lendingtypecode.Text = _lendingtypecode;
                    _info_lendingtypename.Text = _lendingtypename;
                    conn.Close();
                }
            }
            _countryList.Attributes.Add("onKeyPress", "return GetMap()");
        }
    }
}